﻿CREATE TRIGGER [dbo].[trs_Loan_Verification_]
ON [dbo].[t_Loan_Verification]
FOR INSERT,UPDATE,DELETE
AS

IF @@ROWCOUNT = 0 RETURN

--DECLARE @D datetime SET @D = GETDATE()

DECLARE @AccessID int,@RowCount int,@StatusID int,@TablePK int
CREATE TABLE #StatusIDs(StatusID int NOT NULL)
IF NOT EXISTS(SELECT * FROM inserted)BEGIN
	INSERT INTO #StatusIDs SELECT PT.StatusID FROM deleted d INNER JOIN t_VerificationSmart PT ON d.LoanID = PT.LoanID
	SELECT @RowCount = COUNT(*),@AccessID = 2 -- Update
	IF NOT EXISTS(SELECT * FROM #StatusIDs) RETURN
END
ELSE IF NOT EXISTS(SELECT * FROM deleted ) BEGIN
	INSERT INTO #StatusIDs SELECT PT.StatusID FROM inserted i INNER JOIN t_VerificationSmart PT ON i.LoanID = PT.LoanID
	SELECT @RowCount = @@ROWCOUNT,@AccessID = 2 -- Update
END
ELSE BEGIN
	INSERT INTO #StatusIDs SELECT PT.StatusID FROM deleted d INNER JOIN t_VerificationSmart PT ON d.LoanID = PT.LoanID
	SELECT @RowCount = @@ROWCOUNT,@AccessID = 2 -- Update
END

SELECT @StatusID = StatusID,@TablePK = PT.LoanID FROM (SELECT LoanID FROM inserted UNION SELECT LoanID FROM deleted)T
INNER JOIN t_VerificationSmart PT ON T.LoanID = PT.LoanID

/*T*/IF dbo.Config_Trace() = 1 BEGIN SELECT * INTO #inserted FROM inserted DECLARE @CU varbinary(999) SET @CU=COLUMNS_UPDATED()EXEC sp__TraceTrigger @@PROCID,@CU END/*T*/

DECLARE @CUs varbinary(1024) SET @CUs = COLUMNS_UPDATED()

EXEC sec__CheckTableAccess @RowCount,@CUs,@@PROCID,@StatusID,@AccessID,@TablePK

